package org.xlp.db.ddl;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xlp.db.ddl.annotation.XLPCompoundIndex;
import org.xlp.db.ddl.annotation.XLPIndex;
import org.xlp.db.ddl.type.IndexType;
import org.xlp.db.tableoption.annotation.XLPColumn;
import org.xlp.db.tableoption.annotation.XLPEntity;
import org.xlp.db.tableoption.annotation.XLPId;
import org.xlp.db.tableoption.xlpenum.DataType;
import org.xlp.db.tableoption.xlpenum.PrimaryKeyDataType;
import org.xlp.db.tableoption.xlpenum.PrimaryKeyType;
import org.xlp.db.tableoption.xlpenum.TableType;
import org.xlp.db.utils.XLPDBUtil;
import org.xlp.javabean.JavaBeanPropertiesDescriptor;
import org.xlp.javabean.PropertyDescriptor;
import org.xlp.scanner.pkg.ScannerPkg;
import org.xlp.utils.XLPStringUtil;
import org.xlp.utils.XLPVerifedUtil;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

/**
 * <p>
 * 创建时间：2023年9月4日
 * </p>
 * 
 * @author xlp
 * @version 1.0
 * @Description oracle数据库表创建器
 */
public class OracleDBTableCreator extends AbstractTableCreator{
	private final static Logger LOGGER = LoggerFactory.getLogger(OracleDBTableCreator.class);

	/**
	 * 未定义字段长度值
	 */
	public static final int NOT_DEFINE_LEN = -1;

	/**
	 * 字符串类型字段默认长度
	 */
	private static final int CHAR_PRIMARY_KEY_DEFAULT_LENGTH = 64;

	/**
	 * 存储所有表或字段描述说明创建语句
	 */
	private final List<String> comments = new ArrayList<>();

	/**
	 * 存储表主键，索引Sql集合
	 */
	private final List<String> tableKeysAndIndexes = new ArrayList<>();

	/**
	 * 构造函数
	 *
	 * @param connection    数据库连接
	 * @param entityClasses 需要创建数据库表的实体类集合
	 * @throws NullPointerException 假如参数为null，则抛出该异常
	 */
	public OracleDBTableCreator(Connection connection, Set<Class<?>> entityClasses) {
		super(connection, entityClasses);
	}

	/**
	 * 构造函数
	 *
	 * @param connection   数据库连接
	 * @param packageNames 需要创建数据库表的实体类所在的包名称
	 * @throws NullPointerException 假如参数为空，则抛出该异常
	 * @throws TableCreateException 假如通过包名或是实体类失败，则抛出该异常
	 */
	public OracleDBTableCreator(Connection connection, String... packageNames) {
		super(connection, packageNames);
	}

	/**
	 * 构造函数
	 *
	 * @param connection   数据库连接
	 * @param scannerPkg   包扫描器
	 * @param packageNames 需要创建数据库表的实体类所在的包名称
	 * @throws NullPointerException 假如参数为空，则抛出该异常
	 * @throws TableCreateException 假如通过包名或是实体类失败，则抛出该异常
	 */
	public OracleDBTableCreator(Connection connection, ScannerPkg scannerPkg, String... packageNames) {
		super(connection, scannerPkg, packageNames);
	}

	/**
	 * 构造函数
	 *
	 * @param connection 数据库连接
	 * @throws NullPointerException 假如参数为null，则抛出该异常
	 */
	public OracleDBTableCreator(Connection connection) {
		super(connection);
	}

	/**
	 * 通过实体类创建数据库表
	 *
	 * @param entityClass 实体类
	 * @throws NullPointerException 假如参数为null，贼抛出该异常
	 * @throws TableCreateException 假如创建表过程中出现错误，则抛出该异常
	 */
	@Override
	public <T> void createTableByEntityClass(Class<T> entityClass) {
		super.createTableByEntityClass(entityClass);
		executeExtSql();
	}

	/**
	 * 创建表
	 *
	 * @throws TableCreateException 假如创建表过程中出现错误，则抛出该异常
	 */
	@Override
	public void createTables() {
		super.createTables();
		executeExtSql();
	}

	/**
	 * 处理创建表后的其他表字段相关的Sql
	 */
	private void executeExtSql() {
		//创建表或表字段描述
		createComments();
		//创建表主键或索引
		createTableKeysAndIndexes();
		clear();
	}

	private void clear(){
		//创建完后清空
		comments.clear();
		tableKeysAndIndexes.clear();
	}

	/**
	 * 创建表或字段描述说明
	 */
	private void createComments(){
		executeSqls(comments, "创建表描述或字段描述");
	}

	/**
	 * 执行Sql
	 * @param exeSqls
	 * @param description
	 */
	private void executeSqls(List<String> exeSqls, String description){
		boolean isError = LOGGER.isErrorEnabled();
		Statement statement = null;
		try {
			statement = connection.createStatement();
			for (String exeSql : exeSqls) {
				if (isSkipCreateTableException()) {
					try {
						statement.execute(exeSql);
					} catch (Exception e) {
						if (isError) {
							LOGGER.error("创建statement失败，或执行【" + exeSql + "】失败", e);
						}
					}
				} else {
					statement.execute(exeSql);
				}
			}
		} catch (SQLException e) {
			throw new TableCreateException("创建statement或执行" + description + "Sql失败", e);
		} finally {
			try {
				XLPDBUtil.closeStatement(statement);
			} catch (SQLException e) {
				if (isError) {
					LOGGER.error("关闭statement失败！");
				}
			}
		}
	}

	/**
	 * 创建表索引或Key
	 */
	private void createTableKeysAndIndexes(){
		executeSqls(tableKeysAndIndexes, "创建表主键或表索引");
	}

	/**
	 * 根据实体类得到创建表的sql语句
	 * 
	 * @param entityClass
	 *            实体类
	 * @return 假如参数为null，则返回""
	 */
	@Override
	protected String createTableSql(Class<?> entityClass) {
		StringBuilder tableSql = new StringBuilder();
		XLPEntity xlpEntity = entityClass.getAnnotation(XLPEntity.class);
		if (xlpEntity == null) {
			return XLPStringUtil.EMPTY;
		}

		if (xlpEntity.tableType() != TableType.TABLE) {
			if (LOGGER.isInfoEnabled()) {
				LOGGER.info("该实体" + entityClass.getName() + "为视图，无需创建表！");
			}
			return XLPStringUtil.EMPTY;
		}

		String tableName = xlpEntity.tableName();
		checkTableNameOrColumnName(tableName);

		// 形成table部分sql
		tableSql.append("CREATE TABLE ").append(tableName).append("(");

		JavaBeanPropertiesDescriptor<?> javaBeanPropertiesDescriptor = new JavaBeanPropertiesDescriptor<>(entityClass);
		PropertyDescriptor<?>[] columns = javaBeanPropertiesDescriptor.getPds();
		// 处理成数据库表相应的字段
		XLPColumn xlpColumn;
		XLPId xlpId;
		String columnName = null;
		String columnSql;
		boolean isFirstColumn = true;

		for (PropertyDescriptor<?> propertyDescriptor : columns) {
			xlpColumn = propertyDescriptor.getFieldAnnotation(XLPColumn.class);
			if (xlpColumn == null) {
				xlpId = propertyDescriptor.getFieldAnnotation(XLPId.class);
				if (xlpId != null) {
					columnName = xlpId.columnName();
					columnName = XLPStringUtil.isEmpty(columnName) ? propertyDescriptor.getFieldName() : columnName;
					// 给表的列名加上包装字符
					checkTableNameOrColumnName(columnName);
					tableKeysAndIndexes.add(createKeySql(tableName, columnName));
					columnSql = createColumnSql(xlpId, columnName, xlpEntity);
				} else {
					columnSql = XLPStringUtil.EMPTY;
				}
			} else {
				columnName = xlpColumn.columnName();
				columnName = XLPStringUtil.isEmpty(columnName) ? propertyDescriptor.getFieldName() : columnName;
				// 给表的列名加上包装字符
				checkTableNameOrColumnName(columnName);
				columnSql = createColumnSql(xlpColumn, columnName, tableName);
			}

			if (XLPStringUtil.isEmpty(columnSql))
				continue;

			// 拼接每列sql
			if (!isFirstColumn) {
				tableSql.append(",");
			}
			tableSql.append("\n").append(columnSql);
			isFirstColumn = false;

			// 处理索引列
			XLPIndex index = propertyDescriptor.getFieldAnnotation(XLPIndex.class);
			if (index != null) {
				tableKeysAndIndexes.add(createIndexSql(columnName, index, tableName));
			}
		}

		//组合索引
		XLPCompoundIndex compoundIndex = entityClass.getAnnotation(XLPCompoundIndex.class);
		if (compoundIndex != null) {
			createIndexSql(compoundIndex, tableName);
		}

		tableSql.append("\n)");
		String tableComment = xlpEntity.descriptor();
		if (!XLPStringUtil.isEmpty(tableComment)) {
			comments.add(0, createTableComment(tableName, tableComment));
		}
		return tableSql.toString();
	}

	/**
	 * 创建复合索引
	 * @param compoundIndex
	 * @param tableName
	 * @return
	 */
	private void createIndexSql(XLPCompoundIndex compoundIndex, String tableName) {
		StringBuilder sb;
		String[] columns = compoundIndex.column();
		String[] names = compoundIndex.name();
		IndexType[] indexTypes = compoundIndex.indexType();
		int itypeLen = indexTypes.length, nameLen = names.length;
		for(int i = 0, len = columns.length; i < len; i++){
			sb = new StringBuilder("create ");
			IndexType indexType = i < itypeLen ? indexTypes[i] : IndexType.NORMAL;
			switch (indexType) {
				case UNIQUE:
					sb.append("unique index ");
					break;
				case BITMAP:
					sb.append("bitmap index ");
					break;
				default:
					sb.append("index ");
					break;
			}
			String name = i < nameLen ? names[i] : XLPStringUtil.EMPTY;
			if (!XLPStringUtil.isEmpty(name)) {
				checkTableIndexName(name);
				sb.append(name);
			} else {
				sb.append(tableName).append("_").append(XLPStringUtil.createRandomCase(6));
			}
			sb.append(" on ").append(tableName);

			String _cloumn = columns[i];
			if(!XLPStringUtil.isEmpty(_cloumn)){
				sb.append(" (");
				String[] _columns = columns[i].split(",");
				boolean start = true;
				for (String c : _columns) {
					if (!XLPStringUtil.isEmpty(c)) {
						if (!start) {
							sb.append(",");
						}
						checkTableNameOrColumnName(c);
						sb.append(c.trim());
						start = false;
					}
				}
				sb.append(")");
				if (!start){
					tableKeysAndIndexes.add(sb.toString());
				}
			}
		}
	}

	private String createTableComment(String tableName, String tableComment) {
		return String.format("comment on table %s is '%s'", tableName, transferredChar(tableComment));
	}

	/**
	 * 或取创建主键Sql语句
	 * @param tableName
	 * @param columnName
	 * @return
	 */
	private String createKeySql(String tableName, String columnName) {
		return String.format("alter table %s add constraint %s primary key (%s)",
				tableName, tableName + "_" + columnName, columnName);
	}

	/**
	 * 创建索引列sql
	 * 
	 * @param columnName
	 *            数据表列名称(已被处理过， 无需在处理)
	 * @param xlpIndex
	 * @param tableName
	 * @return
	 */
	private String createIndexSql(String columnName, XLPIndex xlpIndex, String tableName) {
		String indexSql = "create %s index %s on %s (%s)";
		Object[] params = new String[4];
		params[3] = columnName;
		params[2] = tableName;
		switch (xlpIndex.indexType()) {
			case UNIQUE:
				params[0] = "unique";
				break;
			case BITMAP:
				params[0] = "bitmap";
				break;
			default:
				params[0] = XLPStringUtil.EMPTY;
				break;
		}
		//索引名称
		String name = xlpIndex.name();
		if (XLPStringUtil.isEmpty(name)) {
			name = tableName + "_" + columnName;
		} else {
			checkTableIndexName(name);
		}
		params[1] = name;
		return String.format(indexSql, params);
	}

	/**
	 * 根据java XLPColumn注解创建数据库表字段sql
	 * 
	 * @param xlpColumn
	 * @param columnName
	 *            数据表列名称(已被处理过， 无需在处理)
	 * @return 假如参数为null，则返回""
	 */
	protected String createColumnSql(XLPColumn xlpColumn, String columnName, String tableName) {
		StringBuilder sb = new StringBuilder(columnName).append(" ");
		if (xlpColumn != null) {
			DataType dataType = xlpColumn.dataType();
			int len = xlpColumn.length();
			int decimalLength = xlpColumn.decimalLength();
			boolean zeroFill = xlpColumn.zeroFill();
			boolean isNull = xlpColumn.isNull();
			String defaultValue = xlpColumn.defaultValue();
			String comment = xlpColumn.descriptor();

			addTypeSql(sb, dataType, len, decimalLength);
			if (zeroFill && XLPStringUtil.NULL_STRING.equals(defaultValue)){
				defaultValue = "0";
			}
			addDefaultValueSql(sb, dataType, defaultValue);

			// 追加字段是否不为空
			if (!isNull) {
				sb.append("NOT NULL ");
			}
			// 判断字段描述是否为空，假如非空，追加字段描述
			if (!XLPStringUtil.isEmpty(comment)) {
				comments.add(createComment(tableName, columnName, comment));
			}
		}
		return sb.toString();
	}

	/**
	 * 添加字段默认值sql(暂时只支持数字类型和字符串类型)
	 * 
	 * @param sb
	 * @param dataType
	 * @param defaultValue
	 */
	/**
	 * 添加字段默认值sql(暂时只支持数字类型和字符串类型)
	 *
	 * @param sb
	 * @param dataType
	 * @param defaultValue
	 */
	private void addDefaultValueSql(StringBuilder sb, DataType dataType, String defaultValue) {
		switch (dataType) {
			case DECIMAL:
			case DOUBLE:
			case FLOAT:
			case NUMERIC:
			case BOOL:
			case BOOLEAN:
			case BIGINT:
			case INT:
			case SMALLINT:
			case TINYINT:
			case NUMBER:
				if (XLPVerifedUtil.isNumber(defaultValue)) {
					sb.append("DEFAULT ").append(defaultValue).append(" ");
				}
				break;
			case CHAR:
			case VARCHAR:
			case VARCHAR2:
			case TEXT:
			case LONGTEXT:
			case MEDIUMTEXT:
			case TINYTEXT:
			case CLOB:
			case LONG:
				if (!XLPStringUtil.NULL_STRING.equals(defaultValue)) {
					sb.append("DEFAULT '").append(transferredChar(defaultValue)).append("' ");
				}
				break;
			default:
				break;
		}
	}

	/**
	 * 添加字段类型所需的sql
	 *
	 * @param sb
	 * @param dataType
	 *            字段类型
	 * @param len
	 *            字段长度
	 * @param decimalLength
	 *            小数部分长度
	 * @return
	 */
	private void addTypeSql(StringBuilder sb, DataType dataType, int len, int decimalLength) {
		boolean addLen = false;
		switch (dataType) {
			case DECIMAL:
			case DOUBLE:
			case FLOAT:
			case NUMERIC:
			case NUMBER:
				if (len == NOT_DEFINE_LEN){
					sb.append(DataType.NUMBER.getDataTypeName());
				} else {
					if (decimalLength == 0){
						sb.append(DataType.NUMBER.getDataTypeName());
						addLen = true;
					} else {
						sb.append(DataType.NUMBER.getDataTypeName()).append("(").append(len).append(",")
								.append(decimalLength).append(") ");
					}
				}
				break;
			case INT:
			case SMALLINT:
			case TINYINT:
			case BOOL:
			case BOOLEAN:
				sb.append(DataType.INT.getDataTypeName()).append(" ");
				break;
			case BIGINT:
				sb.append(DataType.NUMBER.getDataTypeName()).append(" ");
				break;
			case TINYBLOB:
			case BLOB:
			case MEDIUMBLOB:
			case LONGBLOB:
				sb.append(DataType.BLOB.getDataTypeName()).append(" ");
				break;
			case CHAR:
			case VARCHAR2:
				sb.append(dataType.getDataTypeName());
				addLen = true;
				break;
			case VARCHAR:
				sb.append(DataType.VARCHAR2.getDataTypeName());
				addLen = true;
				break;
			case TINYTEXT:
			case TEXT:
			case MEDIUMTEXT:
			case LONGTEXT:
			case CLOB:
				sb.append(DataType.CLOB.getDataTypeName()).append(" ");
				break;
			case LONG:
				sb.append(dataType.getDataTypeName()).append(" ");
				break;
			case DATETIME:
			case TIMESTAMP:
				sb.append(DataType.TIMESTAMP.getDataTypeName()).append(" ");
				break;
			case DATE:
			case TIME:
				sb.append(DataType.DATE.getDataTypeName()).append(" ");
				break;
			default:
				throw new TableCreateException("[" + dataType.getDataTypeName() + "数据库不支持该字段类型！");
		}
		if (addLen){
			// 判断是否定义了字段长度
			if (len != NOT_DEFINE_LEN) {
				sb.append("(").append(len).append(")");
			}
			sb.append(" ");
		}
	}

	/**
	 * 根据java XLPId注解创建数据库表字段sql
	 * 
	 * @param xlpId
	 * @param columnName
	 *            数据表列名称(已被处理过， 无需在处理)
	 * @param entity
	 * 			  XLPEntity注解
	 * @return 假如参数为null，则返回""
	 */
	protected String createColumnSql(XLPId xlpId, String columnName, XLPEntity entity) {
		StringBuilder sb = new StringBuilder(columnName).append(" ");
		if (xlpId != null) {
			PrimaryKeyDataType pkDataType = xlpId.dataType();
			pkDataType = pkDataType == PrimaryKeyDataType.NONE ? entity.primaryKeyDataType() : pkDataType;
			
			int len = xlpId.length();
			len = len == NOT_DEFINE_LEN ? entity.primaryKeyLength() : len;
			
			//主键类型
			PrimaryKeyType keyType = xlpId.type();
			keyType = keyType == PrimaryKeyType.NONE ? entity.primaryKeyType() : keyType;
			if (keyType == PrimaryKeyType.AUTO) {
				if (pkDataType != PrimaryKeyDataType.BIGINT && pkDataType != PrimaryKeyDataType.INT) {
					pkDataType = PrimaryKeyDataType.INT;
					len = NOT_DEFINE_LEN;
				}
			}
			
			DataType dataType;
			switch (pkDataType) {
				case INT:
					dataType = DataType.INT;
					break;
				case CHAR:
					dataType = DataType.CHAR;
					len = len == NOT_DEFINE_LEN ? CHAR_PRIMARY_KEY_DEFAULT_LENGTH : len;
					break;
				case VARCHAR2:
					dataType = DataType.VARCHAR2;
					len = len == NOT_DEFINE_LEN ? CHAR_PRIMARY_KEY_DEFAULT_LENGTH : len;
					break;
				default:
					dataType = DataType.VARCHAR;
					len = len == NOT_DEFINE_LEN ? CHAR_PRIMARY_KEY_DEFAULT_LENGTH : len;
					break;
			}
			
			int decimalLength = 0;
			
			String comment = xlpId.descriptor();
			comment = XLPStringUtil.isEmpty(comment) ? entity.primaryKeyDescriptor() : comment;
			
			addTypeSql(sb, dataType, len, decimalLength);
			// 追加字段是否不为空
			sb.append("NOT NULL ");
			
			// 判断字段描述是否为空，假如非空，追加字段描述
			if (!XLPStringUtil.isEmpty(comment)) {
				comments.add(createComment(entity.tableName(), columnName, comment));
			}
		}
		return sb.toString();
	}

	/**
	 * 获取创建字段描述说明语句
	 * @param tableName
	 * @param columnName
	 * @param comment
	 * @return
	 */
	private String createComment(String tableName, String columnName, String comment) {
		return String.format("comment on column %s.%s is '%s'",
				tableName, columnName, transferredChar(comment));
	}

	/**
	 * 转义特殊字符
	 * 
	 * @param str
	 *            要转义的字符串
	 * @return
	 */
	private String transferredChar(String str) {
		return str.replace("'", "''");
	}
}
